package com.web.webstart.base.excelUtil;

import jodd.bean.BeanUtil;
import jodd.datetime.JDateTime;
import jodd.util.StringUtil;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel帮助类
 *
 * @createTime: 2014-8-19 下午12:03:49
 * @author: zhanglin
 * @version: 0.1
 */
public class ExcelHelper {

    private static ExcelHelper helper = null;

    private ExcelHelper() {

    }

    public static synchronized ExcelHelper getInstanse() {
        if (helper == null) {
            helper = new ExcelHelper();
        }
        return helper;
    }


    /**
     * 将Excel文件导入到list对象
     *
     * @param head 文件头信息
     * @param fis 导入的数据源  new FileInputStream(file)
     * @param cls  保存当前数据的对象
     * @return List
     * 2014-8-19 下午01:17:48
     * @auther zhanglin
     */
    @SuppressWarnings("rawtypes")
    public List importToObjectList(ExcelHead head, InputStream fis, Class cls) {
        List contents = null;
        // 根据excel生成list类型的数据
        List<List> rows;
        try {
            rows = excelFileConvertToList(fis);

            // 删除头信息
            for (int i = 0; i < head.getRowCount(); i++) {
                rows.remove(0);
            }

            // 将表结构转换成Map
            Map<Integer, String> excelHeadMap = convertExcelHeadToMap(head.getColumns());
            // 构建为对象
            contents = buildDataObject(excelHeadMap, head.getColumnsConvertMap(), rows, cls);
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return contents;
    }

    /**
     * 导出数据至Excel文件
     *
     * @param head     报表头信息
     *                 //@param excelHeadConvertMap   需要对数据进行特殊转换的列
     *                 //@param modelFile  模板Excel文件
     *                 //@param outputFile 导出文件
     * @param dataList 导入excel报表的数据来源
     * @return void
     * 2014-8-19 下午01:17:48
     * @auther zhanglin
     */
    @SuppressWarnings("rawtypes")
	public void exportExcelFile(HttpServletResponse response,ExcelHead head, List<?> dataList) {
    	//创建excel
        Workbook wb = new SXSSFWorkbook();
        try {
            //创建一个工作区域
            Sheet sheet = wb.createSheet();
            List<ExcelColumn> excelColumns = head.getColumns();
            Map<String, Map> excelHeadConvertMap = head.getColumnsConvertMap();
            // 将表结构转换成Map
            Map<Integer, String> excelHeadMap = convertExcelHeadToMap(excelColumns);
            // 生成导出数据
            for (int i = 0; i <= dataList.size(); i++) {
                Row row = sheet.createRow(i);
                for (int j = 0; j < excelHeadMap.size(); j++) {
                    if (i == 0) {
                        row.createCell(j).setCellValue(excelColumns.get(j).getFieldDispName());
                    } else {
                        String fieldName = excelHeadMap.get(j);
                        if (fieldName != null) {
                            //普通的字段
                            if (!fieldName.contains(".")) {
                                Object valueObject = BeanUtil.getProperty(dataList.get(i-1), fieldName);
                                // 如果存在需要转换的字段信息，则进行转换
                                if (excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) {
                                    valueObject = excelHeadConvertMap.get(fieldName).get(valueObject);
                                }
                                CellUtil.createCell(row, j, String.valueOf(valueObject));
                            } else {
                                // duandazhi add
                                //类似shopvo.user这种；最多支持2级
                                String farentBeanName = fieldName.split("\\.")[0];
                                String subBeanName = fieldName.split("\\.")[1];
                                Object valueObject1 = BeanUtil.getProperty(dataList.get(i-1), farentBeanName);
                                Object valueObject2 = BeanUtil.getProperty(valueObject1, subBeanName);
                                // 如果存在需要转换的字段信息，则进行转换
                                if (excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) {
                                    valueObject2 = excelHeadConvertMap.get(fieldName).get(valueObject2);
                                }
                                CellUtil.createCell(row, j, String.valueOf(valueObject2));
                            }
                        }
                    }
                    sheet.setColumnWidth(j, (String.valueOf(Math.random()).getBytes().length) * 256);
                }
            }
            //设置默认文件名为当前时间：年月日时分秒 
            String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date()).toString();
            response.reset();
            response.setContentType("application/vnd.ms-excel");        //改成输出excel文件 
            response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");
            // 导出到文件中
            OutputStream fileOut = response.getOutputStream();
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    /**
     * 将报表结构转换成Map
     *
     * @param excelColumns
     * @return void
     * 2014-8-19 下午01:17:48
     * @auther zhanglin
     */
    private Map<Integer, String> convertExcelHeadToMap(List<ExcelColumn> excelColumns) {
        Map<Integer, String> excelHeadMap = new HashMap<Integer, String>();
        for (ExcelColumn excelColumn : excelColumns) {
            if (StringUtil.isEmpty(excelColumn.getFieldName())) {
                continue;
            } else {
                excelHeadMap.put(excelColumn.getIndex(), excelColumn.getFieldName());
            }
        }
        return excelHeadMap;
    }

    /**
     * 将Excel文件内容转换为List对象
     *
     * @param fis excel文件
     * @return List<List>
     * 2014-8-19 下午01:17:48
     * @throws IOException
     * @auther zhanglin
     */
    @SuppressWarnings({"rawtypes", "unused"})
    public List<List> excelFileConvertToList(InputStream fis) throws Exception {
        Workbook wb = WorkbookFactory.create(fis);

        Sheet sheet = wb.getSheetAt(0);

        List<List> rows = new ArrayList<List>();
        for (Row row : sheet) {
            List<Object> cells = new ArrayList<Object>();
            // for (Cell cell : row) { //这种有bug,会出现问题，会跳过空行,导致数据错位！
            for (int j = 0; j < row.getLastCellNum(); ++j) {
                Cell cell = row.getCell(j);
                Object obj = null;

                if (cell != null) {//空白单元
                    CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());

                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            obj = cell.getRichStringCellValue().getString();
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                obj = new JDateTime(cell.getDateCellValue());
                            } else {
                                obj = cell.getNumericCellValue();
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            obj = cell.getBooleanCellValue();
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            obj = cell.getNumericCellValue();
                            break;
                        default:
                            obj = null;
                    }
                }

                cells.add(obj);
            }
            rows.add(cells);
        }
        return rows;
    }

    /**
     * 根据Excel生成数据对象
     *
     * @param excelHeadMap        表头信息
     * @param excelHeadConvertMap 需要特殊转换的单元
     * @param rows
     * @param cls
     * @return void
     * 2014-8-19 下午01:17:48
     * @auther zhanglin
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    private List buildDataObject(Map<Integer, String> excelHeadMap, Map<String, Map> excelHeadConvertMap, List<List> rows, Class cls) {
        List contents = new ArrayList();
        for (List list : rows) {
            // 如果当前第一列中无数据,则忽略当前行的数据
            if (list == null || list.get(0) == null) {
                break;
            }
            // 当前行的数据放入map中,生成<fieldName, value>的形式
            Map<String, Object> rowMap = rowListToMap(excelHeadMap, excelHeadConvertMap, list);

            // 将当前行转换成对应的对象
            Object obj = null;
            try {
                obj = cls.newInstance();
            } catch (InstantiationException ex) {
                ex.printStackTrace();
            } catch (IllegalAccessException ex) {
                ex.printStackTrace();
            }
            BeanUtil.populateBean(obj, rowMap);

            contents.add(obj);
        }
        return contents;
    }

    /**
     * 将行转行成map,生成<fieldName, value>的形式
     *
     * @param excelHeadMap        表头信息
     * @param excelHeadConvertMap excelHeadConvertMap
     * @param list
     * @return Map<String,Object>
     * 2014-8-19 下午01:17:48
     * @auther zhanglin
     */
    @SuppressWarnings("rawtypes")
    private Map<String, Object> rowListToMap(Map<Integer, String> excelHeadMap, Map<String, Map> excelHeadConvertMap, List list) {
        Map<String, Object> rowMap = new HashMap<String, Object>();
        for (int i = 0; i < list.size(); i++) {
            String fieldName = excelHeadMap.get(i);
            // 存在所定义的列
            if (fieldName != null) {
                Object value = list.get(i);
                if (excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) {
                    value = excelHeadConvertMap.get(fieldName).get(value);
                }
                rowMap.put(fieldName, value);
            }
        }
        return rowMap;
    }

}
